#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
@author: Lénia Andrade nº 5607
'''

import csv
import sqlite3


class CreateDB:

    '''
    This method creates the database
    '''
    def createDataBase(self):

        conn = sqlite3.connect('../Rebides.db') #
        c = conn.cursor()

        for y in range(2000, 2011):
            c.execute('''drop table if exists teachers_information_year_{0}'''.format(y))
            c.execute('''create table if not exists teachers_information_year_{0} (teacher_code INTEGER, teacher_name TEXT, degree TEXT, course TEXT,
                category TEXT, regime TEXT, year INTEGER, establishment_type TEXT, establishment TEXT) '''.format(y))
            pass
        self.fillDataBase(conn, c)
        pass
    
    
    
    '''
    This method fills the database with the data contained in csv files
    '''
    def fillDataBase(self, conn, c):

        for x in range (0, 11):
            y = 2000 + x
            f = open("../rebcsv/reb{0}.csv". format(x), "rb")
            reader = csv.reader(f)
            reader.next()
            spamReader = csv.reader(f, delimiter=',')

            for row in spamReader:

                
                c.execute('''insert into teachers_information_year_{0} (teacher_code, teacher_name, degree, course, category, regime, year, establishment_type,
                        establishment) values("{1}","{2}","{3}","{4}","{5}","{6}","{7}","{8}","{9}")'''.\

                          format(y, str(row[0]), str(row[1]).replace('"', '\''), str(row[2]).replace('"', '\''),
                             str(row[3]).replace('"', '\''), str(row[4]).replace('"', '\''),
                             str(row[5]).replace('"', '\''), str(row[6]), str(row[7]).replace('"', '\''),
                             str(row[8]).replace('"', '\'')))
                
                pass
            pass
                
        f.close()
        pass
        conn.commit()
        conn.close()
    pass